package cn.com.callback

import cn.com.callback.CallBackInvoke
import cn.com.elite.EliteBean
import org.apache.commons.logging.Log
import org.apache.commons.logging.LogFactory
import org.slf4j.Logger
import org.slf4j.LoggerFactory

/**
 * 网聊数据汇总定时任务
 * 2023/5/30
 */

public class autoHSTChatData implements CallBackInvoke {
    private static final Logger log = LoggerFactory.getLogger(autoHSTChatData.class);

    public void doCallBack(EliteBean eliteBean, String s, Map map, String s1) {



        long startTime = System.currentTimeMillis();
        long endTime = System.currentTimeMillis();
        log.debug("网聊数据汇总定时任务启动任务content:" + s);
        try {

            //查询重复会话
            String delist_sql = "select session_id from chat_service_detail where session_id!=0 group by session_id having count(session_id)>1 "

            List deleteList =eliteBean.chaxun_sql(delist_sql,"dataSource");
            int a=0;
            for(int i=0;i<deleteList.size();i++){
                Map detMap = deleteList.get(i);
                String detid = detMap.get("session_id").toString();

                String desql="delete from chat_service_detail where session_id ='"+detid+"'";
                a=eliteBean.update(desql,"dataSource");

            }
            log.info("查询重复会话删除数："+a);


            String selectSql = '''SELECT DISTINCT
	upper(
	UUID()) uuid,
	r1.creation_time,
	ifnull( r1.id, '' ) AS id,
	ifnull( r1.chat_session_id, 0 ) AS chat_session_id,
	ifnull( r1.queue_id, '' ) AS queue_id,
	ifnull( r1.client_id, '' ) AS client_id,
	ifnull( r3.isvip, '' ) AS isvip,
	ifnull( r4.start_time, 0 ) start_time,
	ifnull( r4.end_time, 0 ) end_time,
	ifnull( r5.robotid, '' ) robotid,
	ifnull( r2.robottime, 0 ) robottime,
	ifnull( r6.isagent, '' ) isagent,
	ifnull( r7.creation_time, 0 ) AS agent_creation_time,
	ifnull( r7.agent_wait_time, 0 ) agent_wait_time,
	ifnull( r6.agent_is_giveup, '' ) agent_is_giveup,
CASE
		
		WHEN r9.isleave_msg = '是' THEN
		'是' ELSE '否' 
	END AS isleave_msg,
	ifnull( a.to_chat_user_id, '' ) AS staff_id,
	ifnull( from_unixtime( b.maxtime / 1000, '%Y-%m-%d %H:%i:%s' ), '' ) AS maxtime,
CASE
		
		WHEN c.istransfer > 0 THEN
		'是' ELSE '否' 
	END AS istransfer,
	ifnull( c.chat_session_id, '' ) AS zj_chat_session_id,
	ifnull( d.rg_service_duration, 0 ) AS rg_service_duration,
	ifnull( e.cust_zrghsl, 0 ) AS cust_zrghsl,
	ifnull( f.maxtotal, 0 ) AS maxtotal,
	ifnull( f.mintotal, 0 ) AS mintotal,
	ifnull( g.zx_send_sl, '' ) AS zx_send_sl,
	ifnull( h.是否邀评, '' ) AS isyp,
	ifnull( i.是否评价, '' ) AS israting,
CASE
		i.评价满意度 
		WHEN 1 THEN
		'非常满意' 
		WHEN 2 THEN
		'满意' 
		WHEN 3 THEN
		'一般' 
		WHEN 4 THEN
		'不满意' 
		WHEN 5 THEN
		'Very satisfied' 
		WHEN 6 THEN
		'satisfied' 
		WHEN 7 THEN
		'dissatisfied'
		WHEN 8 THEN
		'Neutral'
		WHEN 9 THEN
		'Very dissatisfied' ELSE '未评价' 
	END AS rating,
	ifnull( r18.agentcusttotal, 0 ) AS custtotal,
	ifnull( r8.response_time, 0 ) AS response_time,
	ifnull( r15.在线请求数, 0 ) AS zxnum,
	ifnull( k.访客放弃数, 0 ) AS fknum,
	ifnull( l.未等待应答数, 0 ) AS notwaitnum,
	ifnull( m.等待应答数, 0 ) AS waitnum,
	ifnull( n.在线应答数, 0 ) AS zxydnum,
	ifnull( o.等待放弃数, 0 ) AS waitfqnum,
	ifnull( p.等待转留言数, 0 ) AS waitlynum,
	ifnull( q.总等待数, 0 ) AS waittotal,
	ifnull( r16.离线请求数, 0 ) AS lxnum,
	ifnull( r10.channel, '' ) AS url_from,
	ifnull( r11.wait_time, 0 ) AS wait_time,
	ifnull( r8.total, 0 ) AS total,
	ifnull( r8.Content, 0 ) AS Content,
	ifnull( i.满意度备注, '' ) AS rating_COMMENT,
	ifnull( r4.close_by, '' ) AS close_by,
	ifnull( r5.robot_starttime, 0 ) AS robot_starttime,
	ifnull( r14.全部请求数, 0 ) AS qbqqs,
	ifnull( r14.机器人转人工请求数, 0 ) AS jqrzrgqqs,
	ifnull( r14.直接转人工请求数, 0 ) AS zjzrgqqs,
	ifnull( r15.在线机器人转人工请求数, 0 ) AS zxjqrzrgqqs,
	ifnull( r15.在线直接转人工请求数, 0 ) AS zxzjzrgqqs,
	ifnull( r16.离线机器人转人工请求数, 0 ) AS lxjqrzrgnum,
	ifnull( r16.离线直接转人工请求数, 0 ) AS lxzjzrgnum,
	ifnull( r12.robot_lxs, 0 ) AS robot_lxs,
	ifnull( r17.request_ip, '' ) AS request_ip,
	ifnull( h.push_rating_time, 0 ) AS push_rating_time,
	ifnull( r19.robotcusttotal, 0 ) AS robotcusttotal,
  ifnull( r20.纯机器人请求数, 0 ) AS pure_robot_request,
	now() 
FROM
	(
	SELECT
		chat_session_id,
		creation_time,
		queue_id,
		rank1,
		to_chat_user_id,
		id,
		client_id 
	FROM
		(
		SELECT
			t.chat_session_id,
			t.creation_time,
			t.queue_id,
			@rownum := @rownum + 1,
		IF
			( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
			@order_num := t.chat_session_id,
			to_chat_user_id,
			id,
			client_id 
		FROM
			( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, id, client_id FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND cr.request_type_id != 5 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
			SELECT
				@rownum := 0,
				@order_num := NULL,
				@rank1 := 0 
			) a 
		) result 
	WHERE
		rank1 = 1 
	ORDER BY
		chat_session_id ASC 
	) r1
	LEFT JOIN ( SELECT chat_session_id, sum(( end_time - creation_time )/ 1000 ) AS robottime FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND cr.request_type_id != 5 AND chat_session_id NOT IN ( SELECT cr.chat_session_id FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND request_status_id = 1 ) GROUP BY chat_session_id ) r2 ON r2.chat_session_id = r1.chat_session_id
	LEFT JOIN ( SELECT DISTINCT chat_session_id, CASE WHEN cv.visitor_guid IS NOT NULL THEN '否' ELSE '是' END AS isvip FROM chat_request cr LEFT JOIN chat_visitor cv ON cr.client_id = cv.visitor_guid WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id DESC ) r3 ON r1.chat_session_id = r3.chat_session_id
	LEFT JOIN ( SELECT		cs.id,		cs.start_time,		cs.end_time,	CASE			
			WHEN LENGTH( cs.close_by )= 6 AND cs.close_by NOT LIKE 'BOT%' THEN '客服关闭' WHEN LENGTH( cs.close_by )= 36 THEN '客户关闭' WHEN cs.close_by LIKE 'BOT%' THEN
				'机器人关闭' WHEN cs.close_by = 'cleanupSessionsTimer' THEN '客户放弃' ELSE '' END AS close_by
		FROM chat_request cr left  join	chat_session cs  on cs.id=cr.chat_session_id WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000
		 GROUP BY cs.id,		cs.start_time,		cs.end_time) r4 ON r1.chat_session_id = r4.id
	LEFT JOIN ( select cr.chat_session_id, ifnull( to_chat_user_id, 'BOT022' ) AS robotid, cr.creation_time as robot_starttime from chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 and cr.request_type_id != 5 and chat_session_id not in ( select cr.chat_session_id from chat_request cr where cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 and request_status_id = 1 ) and cr.request_status_id = 12 ) r5 ON r1.chat_session_id = r5.chat_session_id
	LEFT JOIN (
	SELECT DISTINCT
		cr1.chat_session_id,
	CASE
			
			WHEN cr1.to_chat_user_id LIKE 'BOT%' THEN
			'否' ELSE '是' 
		END AS isagent,
	CASE
			
			WHEN cr1.to_chat_user_id IS NULL THEN
			'是' ELSE '否' 
		END AS agent_is_giveup 
	FROM
		chat_request cr
		LEFT JOIN (
		SELECT
			chat_session_id,
			creation_time,
			queue_id,
			rank1,
			to_chat_user_id 
		FROM
			(
			SELECT
				t.chat_session_id,
				t.creation_time,
				t.queue_id,
				@rownum := @rownum + 1,
			IF
				( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
				@order_num := t.chat_session_id,
				to_chat_user_id 
			FROM
				( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
				SELECT
					@rownum := 0,
					@order_num := NULL,
					@rank1 := 0 
				) a 
			) result 
		WHERE
			rank1 = 1 
		ORDER BY
			chat_session_id DESC 
		) cr1 ON cr1.chat_session_id = cr.chat_session_id 
	WHERE
		cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
	ORDER BY
		1 DESC 
	) r6 ON r1.chat_session_id = r6.chat_session_id
	LEFT JOIN (
	SELECT
		cr1.chat_session_id,
		cr1.creation_time,
		( cr1.end_time - cr1.creation_time )/ 1000 AS agent_wait_time
		FROM
			(
			SELECT
				chat_session_id,
				creation_time,
				end_time,
				queue_id,
				rank1,
				to_chat_user_id,
				request_status_id,
				request_type_id,
				from_chat_user_id 
			FROM
				(
				SELECT
					t.chat_session_id,
					t.creation_time,
					end_time,
					t.queue_id,
					@rownum := @rownum + 1,
				IF
					( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
					@order_num := t.chat_session_id,
					to_chat_user_id,
					request_status_id,
					request_type_id,
					from_chat_user_id 
				FROM
					( SELECT chat_session_id, creation_time, end_time, queue_id, to_chat_user_id, request_status_id, request_type_id, from_chat_user_id FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND (( cr.request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' ) OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) ORDER BY chat_session_id ASC, creation_time DESC ) t,(
					SELECT
						@rownum := 0,
						@order_num := NULL,
						@rank1 := 0 
					) a 
				) result 
			WHERE
				rank1 = 1 
			ORDER BY
				chat_session_id DESC 
			) cr1 
		) r7 ON r1.chat_session_id = r7.chat_session_id
		LEFT JOIN ( SELECT chat_session_id, sum( CASE WHEN length( chat_user_id )= 6 AND reply_delay > 0 THEN reply_delay ELSE 0 END ) AS response_time, Count(*) AS Total, Sum( Content_Length ) AS Content FROM chat_message cm WHERE cm.post_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 GROUP BY chat_session_id ORDER BY chat_session_id DESC ) r8 ON r1.chat_session_id = r8.chat_session_id
		LEFT JOIN ( SELECT DISTINCT cr.chat_session_id, CASE WHEN clm.chat_request_id IS NOT NULL THEN '是' ELSE '否' END AS isleave_msg FROM chat_leave_msg clm LEFT JOIN chat_request cr ON cr.id = clm.chat_request_id WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND (( cr.request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' ) OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) ORDER BY chat_session_id DESC ) r9 ON r1.chat_session_id = r9.chat_session_id
		LEFT JOIN (
		SELECT DISTINCT
			cr1.chat_session_id,
			cr1.to_chat_user_id 
		FROM
			chat_request cr
			LEFT JOIN (
			SELECT
				chat_session_id,
				creation_time,
				queue_id,
				rank1,
				to_chat_user_id 
			FROM
				(
				SELECT
					t.chat_session_id,
					t.creation_time,
					t.queue_id,
					@rownum := @rownum + 1,
				IF
					( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
					@order_num := t.chat_session_id,
					to_chat_user_id 
				FROM
					( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND request_type_id != 5 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
					SELECT
						@rownum := 0,
						@order_num := NULL,
						@rank1 := 0 
					) a 
				) result 
			WHERE
				rank1 = 1 
			ORDER BY
				chat_session_id DESC 
			) cr1 ON cr1.chat_session_id = cr.chat_session_id 
		WHERE
			cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
			AND ((
					cr.request_type_id = 4 
					AND from_chat_user_id LIKE 'BOT%' 
					) 
			OR ( cr.request_type_id = 1 AND cr1.to_chat_user_id NOT LIKE 'BOT%' )) 
		) A ON r1.chat_session_id = a.chat_session_id
		LEFT JOIN ( SELECT cm.chat_session_id, max( post_time ) AS maxtime FROM chat_message cm WHERE cm.post_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND length( cm.chat_user_id )= 6 AND chat_user_id NOT LIKE 'BOT%' GROUP BY cm.chat_session_id ) B ON r1.chat_session_id = b.chat_session_id
		LEFT JOIN ( SELECT cr.chat_session_id, sum( CASE WHEN request_type_id = 3 THEN 1 ELSE 0 END ) AS istransfer FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 GROUP BY cr.chat_session_id HAVING sum( CASE WHEN request_type_id = 3 THEN 1 ELSE 0 END ) > 0 ) C ON r1.chat_session_id = c.chat_session_id
		LEFT JOIN (
		SELECT DISTINCT
			cr1.chat_session_id,
			( cr1.agent_end_time - cr1.end_time ) / 1000 AS rg_service_duration 
		FROM
			(
			SELECT
				chat_session_id,
				creation_time,
				queue_id,
				rank1,
				to_chat_user_id,
				agent_end_time,
				end_time 
			FROM
				(
				SELECT
					t.chat_session_id,
					t.creation_time,
					t.queue_id,
					t.agent_end_time,
					t.end_time,
					@rownum := @rownum + 1,
				IF
					( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
					@order_num := t.chat_session_id,
					to_chat_user_id 
				FROM
					( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
					SELECT
						@rownum := 0,
						@order_num := NULL,
						@rank1 := 0 
					) a 
				) result 
			WHERE
				rank1 = 1 
			ORDER BY
				chat_session_id DESC 
			) cr1 
		WHERE
			1 = 1 
			AND ( cr1.to_chat_user_id NOT LIKE 'BOT%' AND cr1.to_chat_user_id IS NOT NULL ) 
		) D ON r1.chat_session_id = d.chat_session_id
		LEFT JOIN (
		SELECT
			cm.Chat_Session_Id AS Session_Id,
			count( DISTINCT cm.id ) AS 'cust_zrghsl' 
		FROM
			chat_request cr
			LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id 
			AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) 
			AND cr.request_status_id = 1 
			AND cm.post_time >= cr.creation_time 
			AND LENGTH( chat_user_id )= 36 
			AND message_type = 0 
		WHERE
			cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
			AND cm.chat_session_id IS NOT NULL 
		GROUP BY
			cm.Chat_Session_Id 
		) E ON a.chat_session_id = e.session_id
		LEFT JOIN (
		SELECT
			cm.Chat_Session_Id AS Session_Id,
			max( reply_delay ) maxtotal,
			min( CASE WHEN reply_delay > 0 THEN reply_delay END ) mintotal 
		FROM
			chat_request cr
			LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id 
			AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) 
			AND cr.request_status_id = 1 
			AND cm.post_time >= cr.creation_time 
		WHERE
			cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
			AND length( cm.chat_user_id ) = 6 
			AND cm.chat_user_id NOT LIKE 'BOT%' 
		GROUP BY
			cm.Chat_Session_Id 
		ORDER BY
			Session_Id DESC 
		) F ON r1.chat_session_id = f.session_id
		LEFT JOIN (
		SELECT
			cm.Chat_Session_Id AS Session_Id,
			count( DISTINCT cm.id ) AS 'zx_send_sl' 
		FROM
			chat_request cr
			LEFT JOIN Chat_Message cm ON cm.chat_session_id = cr.chat_session_id 
			AND ( cr.request_type_id = 4 OR cr.request_type_id = 1 ) 
			AND cr.request_status_id = 1 
			AND cm.post_time >= cr.creation_time 
			AND LENGTH( chat_user_id )= 6 
			AND message_type = 0 
		WHERE
			cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
			AND cm.chat_session_id IS NOT NULL 
		GROUP BY
			cm.Chat_Session_Id 
		) G ON r1.chat_session_id = g.session_id
		LEFT JOIN ( SELECT cs.id, push_rating_time, sum( CASE WHEN push_rating_by IS NOT NULL THEN 1 ELSE 0 END ) '是否邀评' FROM chat_session cs WHERE cs.start_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 GROUP BY cs.id, push_rating_time ) H ON a.chat_session_id = h.id
		LEFT JOIN (
		SELECT
			cr1.chat_session_id,
		CASE	
				WHEN ( cr1.rating_id IS NOT NULL OR cr1.rating_id <> '' ) THEN
				'是' ELSE '否' 
			END AS '是否评价',
			cr1.rating_id AS '评价满意度',
			cr1.RATING_COMMENT AS '满意度备注' 
			from(
			SELECT
				chat_session_id,
				creation_time,
				queue_id,
				rank1,
				to_chat_user_id,
				agent_end_time,
				end_time,
				request_type_id,
				from_chat_user_id,
				request_status_id,
				rating_id,
				RATING_COMMENT
			FROM
				(
				SELECT
					t.chat_session_id,
					t.creation_time,
					t.queue_id,
					t.agent_end_time,
					t.end_time,
					t.request_type_id,
					t.from_chat_user_id,
					t.request_status_id,
					t.RATING_COMMENT,
					t.rating_id,
					@rownum := @rownum + 1,
				IF
					( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
					@order_num := t.chat_session_id,
					to_chat_user_id 
				FROM
					( SELECT cr.chat_session_id, cr.creation_time, cr.queue_id, cr.to_chat_user_id, cr.agent_end_time, cr.end_time, cr.request_type_id, cr.from_chat_user_id, cr.request_status_id,cs.RATING_COMMENT,cs.rating_id FROM chat_request cr left join chat_session cs on cr.chat_session_id = cs.id where cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
					SELECT
						@rownum := 0,
						@order_num := NULL,
						@rank1 := 0 
					) a 
				) result 
			WHERE
				1=1 
			ORDER BY
				chat_session_id DESC 
			) cr1
		WHERE
			1=1 and cr1.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000
			AND ((
					cr1.request_type_id = 4 
					AND cr1.from_chat_user_id LIKE 'BOT%' 
					) 
			OR ( cr1.request_type_id = 1 AND cr1.to_chat_user_id NOT LIKE 'BOT%' )) 
			AND cr1.request_status_id = 1 		
		) I ON r1.chat_session_id = i.chat_session_id
		LEFT JOIN staff s ON a.to_chat_user_id = s.staff_id
		LEFT JOIN (
		SELECT
			count( r1.id ) AS 访客放弃数,
			r1.id 
		FROM
			(
			SELECT
				t.chat_session_id,
				t.creation_time,
				t.queue_id,
				t.agent_end_time,
				t.end_time,
				@rownum := @rownum + 1,
				t.id,
				t.request_type_id,
				t.request_status_id,
			IF
				( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
				@order_num := t.chat_session_id,
				to_chat_user_id 
			FROM
				( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time, cr.id, request_type_id, request_status_id FROM chat_request cr WHERE ( request_status_id = 7 OR request_status_id = 3 OR request_status_id = 4 ) AND cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
				SELECT
					@rownum := 0,
					@order_num := NULL,
					@rank1 := 0 
				) a 
			) r1 
		WHERE
			rank1 = 1 
		GROUP BY
			r1.id 
		) K ON r1.id = k.id
		LEFT JOIN (
		SELECT
			r1.id,
			count( DISTINCT CASE WHEN ( r1.end_time - r1.creation_time )<= 3000 AND request_status_id = 1 THEN r1.CHAT_SESSION_ID ELSE NULL END ) AS 未等待应答数 
		FROM
			(
			SELECT
				t.chat_session_id,
				t.creation_time,
				t.queue_id,
				t.agent_end_time,
				t.end_time,
				@rownum := @rownum + 1,
				t.id,
				t.request_type_id,
				t.request_status_id,
			IF
				( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
				@order_num := t.chat_session_id,
				to_chat_user_id 
			FROM
				( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time, cr.id, request_type_id, request_status_id FROM chat_request cr WHERE (( cr.request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' ) OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) AND cr.request_status_id = 1 AND cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
				SELECT
					@rownum := 0,
					@order_num := NULL,
					@rank1 := 0 
				) a 
			) r1 
		WHERE
			rank1 = 1 
		GROUP BY
			r1.id 
		) L ON r1.id = l.id
		LEFT JOIN (
		SELECT
			r1.id,
			count( DISTINCT CASE WHEN ( r1.end_time - r1.creation_time )> 3000 AND request_status_id = 1 THEN r1.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数 
		FROM
			(
			SELECT
				t.chat_session_id,
				t.creation_time,
				t.queue_id,
				t.agent_end_time,
				t.end_time,
				@rownum := @rownum + 1,
				t.id,
				t.request_type_id,
				t.request_status_id,
			IF
				( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
				@order_num := t.chat_session_id,
				to_chat_user_id 
			FROM
				( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time, cr.id, request_type_id, request_status_id FROM chat_request cr WHERE (( cr.request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' ) OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) AND cr.request_status_id = 1 AND cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
				SELECT
					@rownum := 0,
					@order_num := NULL,
					@rank1 := 0 
				) a 
			) r1 
		WHERE
			rank1 = 1 
		GROUP BY
			r1.id 
		) M ON r1.id = m.id
		LEFT JOIN (
		SELECT
			ts.id,
			sum( ts.未等待应答数 + ts.等待应答数 ) AS 在线应答数 
		FROM
			(
			SELECT
				r1.id,
				count( DISTINCT CASE WHEN ( r1.end_time - r1.creation_time )<= 3000 AND request_status_id = 1 THEN r1.CHAT_SESSION_ID ELSE NULL END ) AS 未等待应答数,
				count( DISTINCT CASE WHEN ( r1.end_time - r1.creation_time )> 3000 AND request_status_id = 1 THEN r1.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数 
			FROM
				(
				SELECT
					t.chat_session_id,
					t.creation_time,
					t.queue_id,
					t.agent_end_time,
					t.end_time,
					@rownum := @rownum + 1,
					t.id,
					t.request_type_id,
					t.request_status_id,
				IF
					( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
					@order_num := t.chat_session_id,
					to_chat_user_id 
				FROM
					( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time, cr.id, request_type_id, request_status_id FROM chat_request cr WHERE (( cr.request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' ) OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) AND cr.request_status_id = 1 AND cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
					SELECT
						@rownum := 0,
						@order_num := NULL,
						@rank1 := 0 
					) a 
				) r1 
			WHERE
				rank1 = 1 
			GROUP BY
				r1.id 
			) ts 
		GROUP BY
			ts.id 
		) N ON r1.id = n.id
		LEFT JOIN (
		SELECT
			cr.chat_session_id,
			count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )> 5000 AND ( request_status_id = 7 OR request_status_id = 3 OR request_status_id = 4 ) AND cr.CHAT_SESSION_ID IS NOT NULL THEN cr.CHAT_SESSION_ID WHEN ( request_status_id = 7 OR request_status_id = 3 OR request_status_id = 4 ) AND cr.CHAT_SESSION_ID IS NULL THEN cr.id ELSE NULL END ) AS 等待放弃数 
		FROM
			chat_request cr 
		WHERE
			cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
			AND ((
					cr.request_type_id = 4 
					AND from_chat_user_id LIKE 'BOT%' 
					) 
			OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) 
		GROUP BY
			cr.chat_session_id 
		) O ON r1.chat_session_id = o.chat_session_id
		LEFT JOIN ( SELECT cr.chat_session_id, sum( CASE WHEN cr.request_status_id = 5 THEN 1 ELSE 0 END ) 等待转留言数 FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND (( cr.request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' ) OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) GROUP BY cr.chat_session_id ) P ON r1.chat_session_id = p.chat_session_id
		LEFT JOIN (
		SELECT
			t.id,
			sum( t.等待放弃数 + t.等待应答数 + t.等待转留言数 ) AS 总等待数 
		FROM
			(
			SELECT
				cr.id,
				count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )> 5000 AND ( request_status_id = 7 OR request_status_id = 3 OR request_status_id = 4 ) AND cr.CHAT_SESSION_ID IS NOT NULL THEN cr.CHAT_SESSION_ID WHEN ( request_status_id = 7 OR request_status_id = 3 OR request_status_id = 4 ) AND cr.CHAT_SESSION_ID IS NULL THEN cr.id ELSE NULL END ) AS 等待放弃数,
				count( DISTINCT CASE WHEN ( cr.end_time - cr.creation_time )> 3000 AND request_status_id = 1 THEN cr.CHAT_SESSION_ID ELSE NULL END ) AS 等待应答数,
				sum( CASE WHEN cr.request_status_id = 5 THEN 1 ELSE 0 END ) 等待转留言数 
			FROM
				chat_request cr 
			WHERE
				cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
				AND ((
						cr.request_type_id = 4 
						AND from_chat_user_id LIKE 'BOT%' 
						) 
				OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) 
			GROUP BY
				cr.id 
			) t 
		GROUP BY
			t.id 
		) Q ON r1.id = q.id
		LEFT JOIN ( SELECT DISTINCT chat_session_id, CASE WHEN cr.channel IS NULL THEN cr.brand ELSE cr.channel END AS channel FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND length( from_chat_user_id )> 6 ) r10 ON r1.chat_session_id = r10.chat_session_id
		LEFT JOIN ( SELECT chat_session_id, sum( ifnull(( cr.end_time - cr.creation_time ) DIV 1000, 0 )) AS wait_time FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 GROUP BY chat_session_id ) r11 ON r1.chat_session_id = r11.chat_session_id
		LEFT JOIN ( SELECT chat_session_id, CASE WHEN sum( CASE WHEN message_type = 1 THEN 1 ELSE 0 END )> 0 THEN sum( CASE WHEN message_type = 1 THEN 1 ELSE 0 END )- 1 ELSE sum( CASE WHEN message_type = 1 THEN 1 ELSE 0 END ) END AS robot_lxs FROM chat_message cm WHERE cm.post_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 GROUP BY chat_session_id ) r12 ON r1.chat_session_id = r12.chat_session_id
			LEFT JOIN (
			SELECT
				count( r1.id ) AS 全部请求数,
				sum( CASE WHEN r1.request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' THEN 1 ELSE 0 END ) AS 机器人转人工请求数,
				sum( CASE WHEN r1.request_type_id = 1 and r1.request_status_id = 1 THEN 1 ELSE 0 END ) AS 直接转人工请求数,
				r1.id 
			FROM
				(
				SELECT
					t.chat_session_id,
					t.creation_time,
					t.queue_id,
					t.agent_end_time,
					t.end_time,
					@rownum := @rownum + 1,
					t.id,
					t.request_type_id,
					t.request_status_id,
				IF
					( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
					@order_num := t.chat_session_id,
					to_chat_user_id,
					t.from_chat_user_id 
				FROM
					( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time, cr.id, request_type_id, request_status_id, from_chat_user_id FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
					SELECT
						@rownum := 0,
						@order_num := NULL,
						@rank1 := 0 
					) a 
				) r1 
			WHERE
				rank1 = 1 
			GROUP BY
				r1.id 
			) r14 ON r1.id = r14.id
			LEFT JOIN (
			SELECT
				count( r1.id ) AS 在线请求数,
				sum( CASE WHEN r1.request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' THEN 1 ELSE 0 END ) AS 在线机器人转人工请求数,
				sum( CASE WHEN r1.request_type_id = 1 and r1.request_status_id = 1 THEN 1 ELSE 0 END ) AS 在线直接转人工请求数,
				r1.id 
			FROM
				(
				SELECT
					t.chat_session_id,
					t.creation_time,
					t.queue_id,
					t.agent_end_time,
					t.end_time,
					@rownum := @rownum + 1,
					t.id,
					t.request_type_id,
					t.request_status_id,
					t.from_chat_user_id,
				IF
					( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
					@order_num := t.chat_session_id,
					to_chat_user_id 
				FROM
					( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time, cr.id, request_type_id, request_status_id, from_chat_user_id FROM chat_request cr WHERE request_status_id <> 6 AND cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
					SELECT
						@rownum := 0,
						@order_num := NULL,
						@rank1 := 0 
					) a 
				) r1 
			WHERE
				rank1 = 1 
			GROUP BY
				r1.id 
			) r15 ON r1.id = r15.id
			LEFT JOIN (
			SELECT
				count( r1.id ) AS 离线请求数,
				sum( CASE WHEN request_type_id = 4 AND from_chat_user_id LIKE 'BOT%' THEN 1 ELSE 0 END ) AS 离线机器人转人工请求数,
				sum( CASE WHEN request_type_id = 1 AND from_chat_user_id NOT LIKE 'BOT%' THEN 1 ELSE 0 END ) AS 离线直接转人工请求数,
				r1.id 
			FROM
				(
				SELECT
					t.chat_session_id,
					t.creation_time,
					t.queue_id,
					t.agent_end_time,
					t.end_time,
					@rownum := @rownum + 1,
					t.id,
					t.request_type_id,
					t.request_status_id,
					t.from_chat_user_id,
				IF
					( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
					@order_num := t.chat_session_id,
					to_chat_user_id 
				FROM
					( SELECT chat_session_id, creation_time, queue_id, to_chat_user_id, agent_end_time, end_time, cr.id, request_type_id, from_chat_user_id, request_status_id FROM chat_request cr WHERE request_status_id = 6 AND cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ORDER BY chat_session_id ASC, creation_time DESC ) t,(
					SELECT
						@rownum := 0,
						@order_num := NULL,
						@rank1 := 0 
					) a 
				) r1 
			WHERE
				rank1 = 1 
			GROUP BY
				r1.id 
			) r16 ON r1.id = r16.id
			LEFT JOIN ( SELECT DISTINCT cr.chat_session_id, request_ip FROM chat_request cr WHERE cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 AND length( from_chat_user_id )> 6 ) r17 ON r1.chat_session_id = r17.chat_session_id
			LEFT JOIN (
			SELECT
				cm.chat_session_id,
				count( CASE WHEN Length( cm.Chat_User_Id )= 6 AND cm.reply_delay > 0 THEN cm.id ELSE NULL END ) agentcusttotal 
			FROM
				chat_request cr
				LEFT JOIN chat_message cm ON cm.chat_session_id = cr.chat_session_id 
			WHERE
				cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
				AND ((
						cr.request_type_id = 4 
						AND from_chat_user_id LIKE 'BOT%' 
						) 
				OR ( cr.request_type_id = 1 AND cr.to_chat_user_id NOT LIKE 'BOT%' )) 
				AND cr.request_status_id = 1 
			GROUP BY
				cr.chat_session_id 
			) r18 ON r1.chat_session_id = r18.chat_session_id
			LEFT JOIN ( SELECT cm.chat_session_id, CASE WHEN count( CASE WHEN chat_user_id LIKE 'BOT%' THEN cm.id ELSE NULL END )= 0 THEN count( CASE WHEN chat_user_id LIKE 'BOT%' THEN cm.id ELSE NULL END ) ELSE count( CASE WHEN chat_user_id LIKE 'BOT%' THEN cm.id ELSE NULL END )- 1 END AS robotcusttotal FROM chat_message cm WHERE cm.post_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 GROUP BY cm.chat_session_id ) r19 ON r1.chat_session_id = r19.chat_session_id
				LEFT JOIN (
				SELECT
					count( 1 ) AS '纯机器人请求数',
					r1.id 
				FROM
					(
					SELECT
						t.chat_session_id,
						t.creation_time,
						t.queue_id,
						t.agent_end_time,
						t.end_time,
						@rownum := @rownum + 1,
						t.id,
						t.request_type_id,
						t.request_status_id,
					IF
						( @order_num = t.chat_session_id, @rank1 := @rank1 + 1, @rank1 := 1 ) AS rank1,
						@order_num := t.chat_session_id,
						to_chat_user_id 
					FROM
						(
						SELECT
							chat_session_id,
							creation_time,
							queue_id,
							to_chat_user_id,
							agent_end_time,
							end_time,
							cr.id,
							request_type_id,
							request_status_id 
						FROM
							chat_request cr 
						WHERE
							1 = 1 
							AND cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
							and request_status_id <> 6 
							AND chat_session_id NOT IN ( SELECT chat_session_id FROM chat_request cr WHERE request_status_id = 1 AND cr.creation_time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 ) 
						ORDER BY
							chat_session_id ASC,
							creation_time DESC 
							) t,(
						SELECT
							@rownum := 0,
							@order_num := NULL,
							@rank1 := 0 
						) a 
					) r1 
				WHERE
					rank1 = 1 
				GROUP BY
					r1.id 
				) r20 ON r20.id = r1.id 
		ORDER BY
	3 ASC''';

            List selectList = eliteBean.chaxun_sql(selectSql, "dataSource");
            endTime = System.currentTimeMillis();
            log.debug("网聊自动任务执行查询返回-耗时：" + (System.currentTimeMillis() - startTime) + "【长度】:" + selectList.size() + "【结果List】：" + selectList.toString());

            String[] doSaveResultSQL = new String[selectList.size()];

            int k = 0;
            for (int i = 0; i < selectList.size(); i++) {

                Map selectMap = selectList.get(i);

                String uuid = selectMap.get("uuid").toString();
                String creation_time = selectMap.get("creation_time").toString();
                String id = selectMap.get("id").toString();
                String chat_session_id = selectMap.get("chat_session_id").toString();
                String queue_id = selectMap.get("queue_id").toString();
                String client_id = selectMap.get("client_id").toString();
                String isvip = selectMap.get("isvip").toString();
                String start_time = selectMap.get("start_time").toString();
                String end_time = selectMap.get("end_time").toString();
                String robotid = selectMap.get("robotid").toString();
                String robottime = selectMap.get("robottime").toString();
                String isagent = selectMap.get("isagent").toString();
                String agent_creation_time = selectMap.get("agent_creation_time").toString();
                String agent_wait_time = selectMap.get("agent_wait_time").toString();
                String agent_is_giveup = selectMap.get("agent_is_giveup").toString();
                String isleave_msg = selectMap.get("isleave_msg").toString();
                String staff_id = selectMap.get("staff_id").toString();
                String maxtime = selectMap.get("maxtime").toString();
                String istransfer = selectMap.get("istransfer").toString();
                String zj_chat_session_id = selectMap.get("zj_chat_session_id").toString();
                String rg_service_duration = selectMap.get("rg_service_duration").toString();
                String cust_zrghsl = selectMap.get("cust_zrghsl").toString();
                String maxtotal = selectMap.get("maxtotal").toString();
                String mintotal = selectMap.get("mintotal").toString();
                String zx_send_sl = selectMap.get("zx_send_sl").toString();
                String isyp = selectMap.get("isyp").toString();
                String israting = selectMap.get("israting").toString();
                String rating = selectMap.get("rating").toString();
                String custtotal = selectMap.get("custtotal").toString();
                String response_time = selectMap.get("response_time").toString();
                String zxnum = selectMap.get("zxnum").toString();
                String fknum = selectMap.get("fknum").toString();
                String notwaitnum = selectMap.get("notwaitnum").toString();
                String waitnum = selectMap.get("waitnum").toString();
                String zxydnum = selectMap.get("zxydnum").toString();
                String waitfqnum = selectMap.get("waitfqnum").toString();
                String waitlynum = selectMap.get("waitlynum").toString();
                String waittotal = selectMap.get("waittotal").toString();
                String lxnum = selectMap.get("lxnum").toString();
                String url_from = selectMap.get("url_from").toString();
                String wait_time = selectMap.get("wait_time").toString();
                String total = selectMap.get("total").toString();
                String Content = selectMap.get("Content").toString();
                String rating_COMMENT = selectMap.get("rating_COMMENT").toString();
                //处理异常的满意度备注转义 例如单引号 双引号
                rating_COMMENT=rating_COMMENT.replaceAll("'","\'");
                rating_COMMENT=rating_COMMENT.replaceAll("'","\"");

                String close_by = selectMap.get("close_by").toString();
                String robot_starttime = selectMap.get("robot_starttime").toString();
                String qbqqs = selectMap.get("qbqqs").toString();
                String jqrzrgqqs = selectMap.get("jqrzrgqqs").toString();
                String zjzrgqqs = selectMap.get("zjzrgqqs").toString();
                String zxjqrzrgqqs = selectMap.get("zxjqrzrgqqs").toString();
                String zxzjzrgqqs = selectMap.get("zxzjzrgqqs").toString();
                String lxjqrzrgnum = selectMap.get("lxjqrzrgnum").toString();
                String lxzjzrgnum = selectMap.get("lxzjzrgnum").toString();
                String robot_lxs = selectMap.get("robot_lxs").toString();
                String request_ip = selectMap.get("request_ip").toString();
                String push_rating_time = selectMap.get("push_rating_time").toString();
                String robotcusttotal = selectMap.get("robotcusttotal").toString();
                String pure_robot_request = selectMap.get("pure_robot_request").toString();
                String selectCheck ='';
                if(chat_session_id!=''){
                    selectCheck= "select rec_guid from chat_service_detail where session_id = '" + chat_session_id + "'";
                }else{
                    selectCheck= "select rec_guid from chat_service_detail where request_id = '" + id + "'";
                }


                List selectCheckList = eliteBean.chaxun_sql(selectCheck, "dataSource");
                if (selectCheckList.size() > 0) {

                    for (int j = 0; j < selectCheckList.size(); j++) {
                        Map selectCheckMap = selectCheckList.get(j);
                        String m_ID = selectCheckMap.get("rec_guid");
                        // 更新当前时间 记录
                        String upSql = "UPDATE chat_service_detail SET " +
                                "creation_time       ='" + creation_time + "'," +
                                "session_id          ='" + chat_session_id + "'," +
                                "request_id          ='" + id + "'," +
                                "queue_id            ='" + queue_id + "'," +
                                "client_id           ='" + client_id + "'," +
                                "isvip               ='" + isvip + "'," +
                                "session_starttime   ='" + start_time + "'," +
                                "session_endtime     ='" + end_time + "'," +
                                "robot_id            ='" + robotid + "'," +
                                "robot_time          ='" + robottime + "'," +
                                "isagent             ='" + isagent + "'," +
                                "agent_starttime     ='" + agent_creation_time + "'," +
                                "agent_wait_time     ='" + agent_wait_time + "'," +
                                "agent_isgiveup      ='" + agent_is_giveup + "'," +
                                "is_LEAVE_MSG        ='" + isleave_msg + "'," +
                                "staff_id            ='" + staff_id + "'," +
                                "agent_end_time      ='" + maxtime + "'," +
                                "istransfer          ='" + istransfer + "'," +
                                "transfer_session_id ='" + zj_chat_session_id + "'," +
                                "agent_time          ='" + rg_service_duration + "'," +
                                "custtotal           ='" + cust_zrghsl + "'," +
                                "max_response_time   ='" + maxtotal + "'," +
                                "min_response_time   ='" + mintotal + "'," +
                                "agenttotal          ='" + zx_send_sl + "'," +
                                "is_Invite_comments  ='" + isyp + "'," +
                                "is_evaluate         ='" + israting + "'," +
                                "rating              ='" + rating + "'," +
                                "cyclesnum           ='" + custtotal + "'," +
                                "response_time       ='" + response_time + "'," +
                                "request_online  	='" + zxnum + "'," +
                                "visitor_give_up     ='" + fknum + "'," +
                                "no_wait_response    ='" + notwaitnum + "'," +
                                "wait_response       ='" + waitnum + "'," +
                                "online_response     ='" + zxydnum + "'," +
                                "wait_give_up        ='" + waitfqnum + "'," +
                                "wait_to_message     ='" + waitlynum + "'," +
                                "total_wait          ='" + waittotal + "'," +
                                "request_offline     ='" + lxnum + "'," +
                                "url_from            ='" + url_from + "'," +
                                "wait_time           ='" + wait_time + "'," +
                                "total               ='" + total + "'," +
                                "Content             ='" + Content + "'," +
                                "rating_remarks      ='" + rating_COMMENT + "'," +
                                "close_by            ='" + close_by + "'," +
                                "robot_starttime     ='" + robot_starttime + "'," +
                                "qbqqs               ='" + qbqqs + "'," +
                                "jqrzrgqqs           ='" + jqrzrgqqs + "'," +
                                "zjzrgqqs            ='" + zjzrgqqs + "'," +
                                "zxjqrzrgqqs         ='" + zxjqrzrgqqs + "'," +
                                "zxzjzrgqqs          ='" + zxzjzrgqqs + "'," +
                                "lxjqrzrgnum         ='" + lxjqrzrgnum + "'," +
                                "lxzjzrgnum          ='" + lxzjzrgnum + "'," +
                                "robot_lxs           ='" + robot_lxs + "'," +
                                "request_ip          ='" + request_ip + "'," +
                                "yptime              ='" + push_rating_time + "'," +
                                "robotcusttotal      ='" + robotcusttotal + "'," +
                                "pure_robot_request      ='" + pure_robot_request + "'" +
                                "WHERE	rec_guid   ='" + m_ID + "' ";
                        log.debug('更新当前时间的记录SQL' + upSql);
                        doSaveResultSQL[i] = upSql;
                    }
                } else {

                    // 插入该条记录
                    String insertSql = "INSERT INTO chat_service_detail (" +
                            "    rec_guid ," +
                            "    creation_time ," +
                            "    request_id ," +
                            "    session_id ," +
                            "    queue_id ," +
                            "    client_id ," +
                            "    isvip ," +
                            "    session_starttime ," +
                            "    session_endtime ," +
                            "    robot_id ," +
                            "    robot_time ," +
                            "    isagent ," +
                            "    agent_starttime ," +
                            "    agent_wait_time ," +
                            "    agent_isgiveup ," +
                            "    is_LEAVE_MSG ," +
                            "    staff_id ," +
                            "    agent_end_time ," +
                            "    istransfer ," +
                            "    transfer_session_id ," +
                            "    agent_time ," +
                            "    custtotal ," +
                            "    max_response_time ," +
                            "    min_response_time ," +
                            "    agenttotal ," +
                            "    is_Invite_comments ," +
                            "    is_evaluate ," +
                            "    rating ," +
                            "    cyclesnum ," +
                            "    response_time ," +
                            "    request_online ," +
                            "    visitor_give_up ," +
                            "    no_wait_response ," +
                            "    wait_response ," +
                            "    online_response ," +
                            "    wait_give_up ," +
                            "    wait_to_message ," +
                            "    total_wait ," +
                            "    request_offline ," +
                            "    url_from ," +
                            "    wait_time ," +
                            "    total ," +
                            "    Content ," +
                            "    rating_remarks ," +
                            "    close_by ," +
                            "    robot_starttime ," +
                            "    qbqqs ," +
                            "    jqrzrgqqs ," +
                            "    zjzrgqqs ," +
                            "    zxjqrzrgqqs ," +
                            "    zxzjzrgqqs ," +
                            "    lxjqrzrgnum ," +
                            "    lxzjzrgnum ," +
                            "    robot_lxs ," +
                            "    request_ip ," +
                            "    yptime ," +
                            "    robotcusttotal ," +
                            "    pure_robot_request ," +
                            "    createddate" +
                            "    ) values(" +
                            "    '" + uuid + "'," +
                            "    '" + creation_time + "'," +
                            "    '" + id + "'," +
                            "    '" + chat_session_id + "'," +
                            "    '" + queue_id + "'," +
                            "    '" + client_id + "'," +
                            "    '" + isvip + "'," +
                            "    '" + start_time + "'," +
                            "    '" + end_time + "'," +
                            "    '" + robotid + "'," +
                            "    '" + robottime + "'," +
                            "    '" + isagent + "'," +
                            "    '" + agent_creation_time + "'," +
                            "    '" + agent_wait_time + "'," +
                            "    '" + agent_is_giveup + "'," +
                            "    '" + isleave_msg + "'," +
                            "    '" + staff_id + "'," +
                            "    '" + maxtime + "'," +
                            "    '" + istransfer + "'," +
                            "    '" + zj_chat_session_id + "'," +
                            "    '" + rg_service_duration + "'," +
                            "    '" + cust_zrghsl + "'," +
                            "    '" + maxtotal + "'," +
                            "    '" + mintotal + "'," +
                            "    '" + zx_send_sl + "'," +
                            "    '" + isyp + "'," +
                            "    '" + israting + "'," +
                            "    '" + rating + "'," +
                            "    '" + custtotal + "'," +
                            "    '" + response_time + "'," +
                            "    '" + zxnum + "'," +
                            "    '" + fknum + "'," +
                            "    '" + notwaitnum + "'," +
                            "    '" + waitnum + "'," +
                            "    '" + zxydnum + "'," +
                            "    '" + waitfqnum + "'," +
                            "    '" + waitlynum + "'," +
                            "    '" + waittotal + "'," +
                            "    '" + lxnum + "'," +
                            "    '" + url_from + "'," +
                            "    '" + wait_time + "'," +
                            "    '" + total + "'," +
                            "    '" + Content + "'," +
                            "    '" + rating_COMMENT + "'," +
                            "    '" + close_by + "'," +
                            "    '" + robot_starttime + "'," +
                            "    '" + qbqqs + "'," +
                            "    '" + jqrzrgqqs + "'," +
                            "    '" + zjzrgqqs + "'," +
                            "    '" + zxjqrzrgqqs + "'," +
                            "    '" + zxzjzrgqqs + "'," +
                            "    '" + lxjqrzrgnum + "'," +
                            "    '" + lxzjzrgnum + "'," +
                            "    '" + robot_lxs + "'," +
                            "    '" + request_ip + "'," +
                            "    '" + push_rating_time + "'," +
                            "    '" + robotcusttotal + "'," +
                            "    '" + pure_robot_request + "'," +
                            "     now())";
                    log.debug('插入当前时间的记录SQL' + insertSql);
                    doSaveResultSQL[i] = insertSql;

                }

                k = k + 1;
            }

            if (k > 0) {
                List doSaveResultSQLList = eliteBean.updateBatch(doSaveResultSQL, "dataSource");
                log.debug("网聊数据定时任务处理End-耗时:" + (System.currentTimeMillis() - endTime) + " 结果:" + doSaveResultSQLList.toString());
            } else {

                log.warn("网聊数据定时任务处理End...SQL空");
            }

            log.debug("网聊数据定时任务处理结束-总耗时:" + (System.currentTimeMillis() - startTime));


        } catch (Exception e) {
            log.error("网聊数据定时任务操作异常", e);
        }
    }

}